/*
* autor: joão pedro brites raposo alexandre
* data criação: 2012/01/29
* linguagem: c sharp
* modulo: functions.cs
* versão: v1.0
*/

using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using LumenWorks.Framework.IO.Csv;
using System.Linq;
using System.Text;


namespace tp2_source_code
{
	class Functions
	{

		
		/*****************************************************************************
		* Corpo do programa
		* cria base de dados
		* load da base de dados com informaçao dos ficheiros csv
		****************************************************************************/
		public static void functions (string[] args)
		{
		}
		
		
		
		/*****************************************************************************
		*Criaçao da base de dados
		*
		*****************************************************************************/
		public static void db_creation()
		{
			try
			{
				//Cria Base de Dados
	            Console.WriteLine("Criando base de dados...");
				SQLiteConnection connect = new SQLiteConnection("Data source=docentes.db");
	            //ligaçao base de dados
				connect.Open();
				//
	            SQLiteTransaction transact = connect.BeginTransaction();
	            SQLiteCommand cmd = connect.CreateCommand();
				//query
	            cmd.CommandText = "DROP TABLE IF EXISTS docentes";
				//executar query
	            cmd.ExecuteNonQuery();
				//query
	            cmd.CommandText = "CREATE TABLE IF NOT EXISTS docentes (cod_docente text, nome text," +
	                                                        "ultimo_grau text,curso text,categoria text," +
	                                                        "regime_tempo text,ano_lectivo text," +
	                                                        "tipo_estabelecimento text,nome_estabelecimento text)";
				//executar query
	            cmd.ExecuteNonQuery();
				Console.WriteLine("Base de dados criada.");

				//load dos ficheiros csv na base de dados
				Console.WriteLine("Carregando base de dados...");
				//quantidade ficheiros
				int quantidade_ficheiros = 10;//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ALTERAR PARA 10
				for (int i = 0; i <= quantidade_ficheiros; i++)
	            {
					//nome ficheiro csv
					String filename = "reb" + i + ".csv";
					Console.WriteLine(filename);
					//abrir ficheiros csv
	                CsvReader fileReader = new CsvReader( new StreamReader(filename), true);
					//query
	                cmd.CommandText = "INSERT INTO docentes (cod_docente,nome,ultimo_grau,curso,categoria,regime_tempo," +
	                                                             "ano_lectivo,tipo_estabelecimento,nome_estabelecimento)" +
																 "VALUES(?,?,?,?,?,?,?,?,?)";
					//cria parametro de entrada db
					for(int x = 0; x < 9; x++)
					{
						cmd.Parameters.Add(cmd.CreateParameter());
					}
	
					//enquanto existir linhas no filereader
	                while (fileReader.ReadNextRecord())
	                {
						//adiciona valor na posiçao do parametro db
	                    for (int j = 0; j < 9; j++)
	                    {
	                        cmd.Parameters[j].Value = fileReader[j];
	                    }
						//executar query
	                    cmd.ExecuteNonQuery();
	                }
	            }			
				
	            transact.Commit();
				//fichar ligaçao
	            connect.Close();
	            Console.WriteLine("Base de Dados carregada.");
			}
			catch(Exception e)
			{
				Console.WriteLine(e);
			}
		}


		/*****************************************************************************
		*query: docentes por categoria no sistema de ensino superior,
		*			por estabelecimento, por ano
		*****************************************************************************/
		public static void query_docentes_categoria_estabelecimento_ano()
		{
			Console.WriteLine("Consulta docentes por categoria por estabelecimento por ano...");
			try
			{
				//Cria ligaçao Base de Dados
				SQLiteConnection connect = new SQLiteConnection("Data source=docentes.db");
				//ligaçao base de dados
				connect.Open();
				//
		        SQLiteTransaction transact = connect.BeginTransaction();
		        SQLiteCommand cmd = connect.CreateCommand();
	
				//query base de dados
		        cmd.CommandText = "SELECT * FROM docentes";
				
				//nova lista de listas
				List<List<string>> lista = new List<List<string>>();
				
				//dados para trabalhar
				SQLiteDataReader dados = cmd.ExecuteReader();
				
				//enquanto existir dados
				while(dados.Read())
				{
					//cria lista temporaria
					List<string> listaTemporaria = new List<string>();
					
					//adiciona na lista os dados dos campos de um registo
					for(int a = 0; a <dados.FieldCount; a++)
					{
						listaTemporaria.Add((string)dados[a]);
					}
					//adiciona listaTemporaria a lista
					lista.Add(listaTemporaria);
				}
				
	            transact.Commit();
				//fechar ligaçao
	            connect.Close();
				
//				//teste impressao dados
//				Console.WriteLine(lista.Count());
//				for ( int s = 0; s <=lista.Count(); s++)
//					for ( int i = 0; i <=8; i++)
//						Console.WriteLine(lista[s][i]);
				
				
				//utilizando o Link para efetuar a consulta	
				var data = from r in lista
						   orderby r[6], r[8], r[4] ascending
						   select r;
				
				Console.WriteLine("Consulta finalizada.");
				
				//criando nome e cabeçalho do ficheiro csv
	            string nome_ficheiro = "docentes_por_categoria_por_estabelecimento_por_ano.csv";
	            string header_ficheiro = "Ano Lectivo," +
	                            		 "Nome de Estabelecimento," +
	                            		 "Categoria," +
	                            		 "Nome do Docente," +
	                            		 "Codigo do Docente," +
	                            		 "Ultimo Grau," +
	                            		 "Curso," +
	                            		 "Regime de Tempo," +
	                            		 "Tipo de Estabelecimento";

				//escrevendo informaçao da query na lista
				List<string> lista_dados = new List<string>();
				foreach(var r in data)
				{
					//construçao da string
					StringBuilder str_line = new StringBuilder();
					str_line.Append(r[6] + "," +  r[8] + "," + r[4] + 
					                "," + r[1] + "," + r[0] + "," + r[2] +
					                "," + r[3] + "," + r[5] + "," + r[7]);
					//escrever string na lista
					lista_dados.Add(str_line.ToString());

				}
				
				//escrevendo novo ficheiro csv com recurso a metodo
            	writeCsvFile(lista_dados, nome_ficheiro, header_ficheiro);
				
			}
			catch(Exception e)
			{
				Console.WriteLine("Base de dados nao encontrada. Carregue a base de dados.");
				Console.WriteLine(e);
			}
		}

		
		
		/*****************************************************************************
		*query: numero de docentes por categoria no sistema de ensino superior,
		*			por estabelecimento, por ano
		*****************************************************************************/
		public static void query_numero_docentes_categoria_estabelecimento_ano()
		{
			Console.WriteLine("Consulta numero de docentes por categoria por estabelecimento por ano...");
			try
			{
				//Cria ligaçao Base de Dados
				SQLiteConnection connect = new SQLiteConnection("Data source=docentes.db");
				//ligaçao base de dados
				connect.Open();
				//
		        SQLiteTransaction transact = connect.BeginTransaction();
		        SQLiteCommand cmd = connect.CreateCommand();
	
				//query base de dados
		        cmd.CommandText = "SELECT COUNT(nome), categoria, nome_estabelecimento, ano_lectivo FROM docentes " +
								  "GROUP BY categoria, nome_estabelecimento, ano_lectivo " +
								  "ORDER BY categoria, nome_estabelecimento";
				
				//nova lista de listas
				List<List<string>> lista = new List<List<string>>();
				
				//dados para trabalhar
				SQLiteDataReader dados = cmd.ExecuteReader();
				
				//enquanto existir dados
				while(dados.Read())
				{
					//cria lista temporaria
					List<string> listaTemporaria = new List<string>();
					
					//adiciona na lista os dados dos campos de um registo
					for(int a = 0; a <dados.FieldCount; a++)
					{
						listaTemporaria.Add(dados[a].ToString());
					}
					//adiciona listaTemporaria a lista
					lista.Add(listaTemporaria);
				}
				
	            transact.Commit();
				//fechar ligaçao
	            connect.Close();
				
//				//teste impressao dados
//				Console.WriteLine(lista.Count());
//				for ( int s = 0; s <=lista.Count(); s++)
//					for ( int i = 0; i <=lista[s].Count(); i++)
//						Console.WriteLine(lista[s][i]);

				//utilizando o Link para efetuar a consulta	
				var data = from r in lista
						   //orderby r[6], r[8], r[4] ascending
						   select r;
				
				Console.WriteLine("Consulta finalizada.");
				
				//criando nome e cabeçalho do ficheiro csv
	            string nome_ficheiro = "total_docentes_por_categoria_por_estabelecimento_por_ano.csv";
	            string header_ficheiro = "Ano Lectivo," +
	                            		 "Nome de Estabelecimento," +
	                            		 "Categoria," +
	                            		 "Total de Docentes";

				//escrevendo informaçao da query na lista
				List<string> lista_dados = new List<string>();
				foreach(var r in data)
				{
					//construçao da string
					StringBuilder str_line = new StringBuilder();
					str_line.Append(r[3] + "," +  r[2] + "," + r[1] +     "," + r[0]);
					//escrever string na lista
					lista_dados.Add(str_line.ToString());

				}
				
				//escrevendo novo ficheiro csv com recurso a metodo
            	writeCsvFile(lista_dados, nome_ficheiro, header_ficheiro);
			
			}
			catch(Exception e)
			{
				Console.WriteLine("Base de dados nao encontrada. Carregue a base de dados.");
				Console.WriteLine(e);
			}
		}

			
		
		/*****************************************************************************
		*query: docentes por tipo de grau (nivel de doutoramento, nivel de mestrado, nivel licenciatura)
		*			por estabelecimento, por ano
		*****************************************************************************/
		public static void query_docentes_tipo_grau_estabelecimento_ano()
		{
			Console.WriteLine("Consulta docentes por tipo de grau por estabelecimento por ano...");
			try
			{
				//Cria ligaçao Base de Dados
				SQLiteConnection connect = new SQLiteConnection("Data source=docentes.db");
				//ligaçao base de dados
				connect.Open();
				//
			    SQLiteTransaction transact = connect.BeginTransaction();
			    SQLiteCommand cmd = connect.CreateCommand();
		
				//query base de dados
			    cmd.CommandText = "SELECT * FROM docentes where ultimo_grau = 'Doutoramento' or ultimo_grau = 'Mestrado' or ultimo_grau = 'Licenciatura'";
	
				//nova lista de listas
				List<List<string>> lista = new List<List<string>>();
					
				//dados para trabalhar
				SQLiteDataReader dados = cmd.ExecuteReader();
				
				//enquanto existir dados
				while(dados.Read())
				{
					//cria lista temporaria
					List<string> listaTemporaria = new List<string>();
					
					//adiciona na lista os dados dos campos de um registo
					for(int a = 0; a <dados.FieldCount; a++)
					{
						listaTemporaria.Add((string)dados[a]);
					}
					//adiciona listaTemporaria a lista
					lista.Add(listaTemporaria);
				}
				
		        transact.Commit();
				//fechar ligaçao
		        connect.Close();
				
//				//teste impressao dados
//				Console.WriteLine(lista.Count());
//				for ( int s = 0; s <=lista.Count(); s++)
//					for ( int i = 0; i <=8; i++)
//						Console.WriteLine(lista[s][i]);
				
					//utilizando o Link para efetuar a consulta	
					var data = from r in lista
							   orderby r[6], r[8], r[2] ascending
							   select r;
					//print
					Console.WriteLine("Consulta finalizada.");
					
					//criando nome e cabeçalho do ficheiro csv
		            string nome_ficheiro = "docentes_por_grau_por_estabelecimento_por_ano.csv";
		            string header_ficheiro = "Ano Lectivo," +
		                            		 "Nome de Estabelecimento," +
		                            		 "Ultimo Grau," +
		                            		 "Nome do Docente," +
		                            		 "Codigo do Docente," +
		                            		 "Categoria," +
		                            		 "Curso," +
		                            		 "Regime de Tempo," +
		                            		 "Tipo de Estabelecimento";
	
					//escrevendo informaçao da query na lista
					List<string> lista_dados = new List<string>();
					foreach(var r in data)
					{
						//construçao da string
						StringBuilder str_line = new StringBuilder();
						str_line.Append(r[6] + "," +  r[8] + "," + r[2] + 
						                "," + r[1] + "," + r[0] + "," + r[4] +
						                "," + r[3] + "," + r[5] + "," + r[7]);
						//escrever string na lista
						lista_dados.Add(str_line.ToString());
	
					}
					
					//escrevendo novo ficheiro csv com recurso a metodo
	            	writeCsvFile(lista_dados, nome_ficheiro, header_ficheiro);
			}
			catch(Exception e)
			{
				Console.WriteLine("Base de dados nao encontrada. Carregue a base de dados.");
				Console.WriteLine(e);
			}
			
		}//fim funcao
		
		
		
		/*****************************************************************************
		*query: numero de docentes por tipo de grau (nivel de doutoramento, nivel de mestrado, nivel licenciatura)
		*			por estabelecimento, por ano
		*****************************************************************************/
		public static void query_numero_docentes_tipo_grau_estabelecimento_ano()
		{
			Console.WriteLine("Consulta numero de docentes por tipo de grau por estabelecimento por ano...");
			try
			{
				//Cria ligaçao Base de Dados
				SQLiteConnection connect = new SQLiteConnection("Data source=docentes.db");
				//ligaçao base de dados
				connect.Open();
				//
		        SQLiteTransaction transact = connect.BeginTransaction();
		        SQLiteCommand cmd = connect.CreateCommand();
	
				//query base de dados	        
				cmd.CommandText = "SELECT COUNT(nome), ultimo_grau, nome_estabelecimento, ano_lectivo FROM docentes " +
								  "WHERE ultimo_grau = 'Doutoramento' or ultimo_grau = 'Mestrado' or ultimo_grau = 'Licenciatura' " +
								  "GROUP BY categoria, nome_estabelecimento, ano_lectivo " +
								  "ORDER BY categoria, nome_estabelecimento";
				
				//nova lista de listas
				List<List<string>> lista = new List<List<string>>();
				
				//dados para trabalhar
				SQLiteDataReader dados = cmd.ExecuteReader();
				
				//enquanto existir dados
				while(dados.Read())
				{
					//cria lista temporaria
					List<string> listaTemporaria = new List<string>();
					
					//adiciona na lista os dados dos campos de um registo
					for(int a = 0; a <dados.FieldCount; a++)
					{
						listaTemporaria.Add(dados[a].ToString());
					}
					//adiciona listaTemporaria a lista
					lista.Add(listaTemporaria);
				}
				
	            transact.Commit();
				//fechar ligaçao
	            connect.Close();
				
//				//teste impressao dados
//				Console.WriteLine(lista.Count());
//				for ( int s = 0; s <=lista.Count(); s++)
//					for ( int i = 0; i <=lista[s].Count(); i++)
//						Console.WriteLine(lista[s][i]);

				//utilizando o Link para efetuar a consulta	
				var data = from r in lista
						   //orderby r[6], r[8], r[4] ascending
						   select r;
				
				Console.WriteLine("Consulta finalizada.");
				
				//criando nome e cabeçalho do ficheiro csv
	            string nome_ficheiro = "total_docentes_por_tipo_grau_por_estabelecimento_por_ano.csv";
	            string header_ficheiro = "Ano Lectivo," +
	                            		 "Nome de Estabelecimento," +
	                            		 "Categoria," +
	                            		 "Total de Docentes";

				//escrevendo informaçao da query na lista
				List<string> lista_dados = new List<string>();
				foreach(var r in data)
				{
					//construçao da string
					StringBuilder str_line = new StringBuilder();
					str_line.Append(r[3] + "," +  r[2] + "," + r[1] +     "," + r[0]);
					//escrever string na lista
					lista_dados.Add(str_line.ToString());

				}
				
				//escrevendo novo ficheiro csv com recurso a metodo
            	writeCsvFile(lista_dados, nome_ficheiro, header_ficheiro);
			
			}
			catch(Exception e)
			{
				Console.WriteLine("Base de dados nao encontrada. Carregue a base de dados.");
				Console.WriteLine(e);
			}
		}
		
		
		/*****************************************************************************
		*criar ficheiro csv
		*
		*****************************************************************************/
		public static void writeCsvFile(List<string> lista_dados, string nome_ficheiro, string header_ficheiro)
		{
			Console.WriteLine("Criando ficheiro csv...");
			try
			{
				//eliminando ficheiro caso exista
				File.Delete(nome_ficheiro);
				//criando editor streamwriter encodin utf-8
				StreamWriter csv_file = new StreamWriter(nome_ficheiro, true, System.Text.Encoding.UTF8);
				//escrevendo cabeçalho
				csv_file.WriteLine(header_ficheiro);
				
				//escrevendo informaçao da query
				foreach(var r in lista_dados)
				{
					csv_file.WriteLine(r);
				}
				
				//fechando ligaçao ficheiro csv
				csv_file.Close();
				//print
				Console.WriteLine("Ficheiro csv criado.");
			}
			catch(Exception e)
			{
				Console.WriteLine(e);
			}
		}

		
	}//end class
}

